import 'dart:async';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import '../models/mood_record.dart';
import '../models/media_attachment.dart';
import '../models/mood_type.dart';

/// 数据库助手类
/// 负责SQLite数据库的创建、升级和基本操作
class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;

  /// 数据库名称
  static const String _databaseName = 'cloud_mood.db';
  
  /// 数据库版本
  static const int _databaseVersion = 1;

  /// 情绪记录表名
  static const String moodRecordsTable = 'mood_records';
  
  /// 媒体附件表名
  static const String mediaAttachmentsTable = 'media_attachments';

  /// 单例构造函数
  DatabaseHelper._internal();

  /// 获取数据库助手实例
  factory DatabaseHelper() => _instance;

  /// 获取数据库实例
  Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  /// 初始化数据库
  Future<Database> _initDatabase() async {
    final databasesPath = await getDatabasesPath();
    final path = join(databasesPath, _databaseName);

    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  /// 创建数据库表
  Future<void> _onCreate(Database db, int version) async {
    // 创建情绪记录表
    await db.execute('''
      CREATE TABLE $moodRecordsTable (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mood_type TEXT NOT NULL,
        diary_content TEXT,
        created_at TEXT NOT NULL,
        updated_at TEXT NOT NULL,
        intensity INTEGER NOT NULL DEFAULT 5,
        tags TEXT,
        weather TEXT,
        location TEXT
      )
    ''');

    // 创建媒体附件表
    await db.execute('''
      CREATE TABLE $mediaAttachmentsTable (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        mood_record_id INTEGER NOT NULL,
        type INTEGER NOT NULL,
        file_path TEXT NOT NULL,
        file_name TEXT NOT NULL,
        file_size INTEGER NOT NULL,
        created_at TEXT NOT NULL,
        thumbnail_path TEXT,
        duration INTEGER,
        FOREIGN KEY (mood_record_id) REFERENCES $moodRecordsTable (id) ON DELETE CASCADE
      )
    ''');

    // 创建索引以提高查询性能
    await db.execute('CREATE INDEX idx_mood_records_created_at ON $moodRecordsTable (created_at)');
    await db.execute('CREATE INDEX idx_mood_records_mood_type ON $moodRecordsTable (mood_type)');
    await db.execute('CREATE INDEX idx_media_attachments_mood_record_id ON $mediaAttachmentsTable (mood_record_id)');
  }

  /// 数据库升级
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // 在这里处理数据库版本升级逻辑
    // 例如：添加新列、创建新表等
  }

  /// 插入情绪记录
  /// [record] 要插入的情绪记录
  /// 返回插入记录的ID
  Future<int> insertMoodRecord(MoodRecord record) async {
    final db = await database;
    return await db.insert(moodRecordsTable, record.toMap());
  }

  /// 更新情绪记录
  /// [record] 要更新的情绪记录
  /// 返回受影响的行数
  Future<int> updateMoodRecord(MoodRecord record) async {
    final db = await database;
    return await db.update(
      moodRecordsTable,
      record.toMap(),
      where: 'id = ?',
      whereArgs: [record.id],
    );
  }

  /// 删除情绪记录
  /// [id] 要删除的记录ID
  /// 返回受影响的行数
  Future<int> deleteMoodRecord(int id) async {
    final db = await database;
    return await db.delete(
      moodRecordsTable,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 根据ID获取情绪记录
  /// [id] 记录ID
  /// 返回情绪记录，如果不存在则返回null
  Future<MoodRecord?> getMoodRecord(int id) async {
    final db = await database;
    final maps = await db.query(
      moodRecordsTable,
      where: 'id = ?',
      whereArgs: [id],
    );

    if (maps.isNotEmpty) {
      final attachments = await getMediaAttachmentsByRecordId(id);
      return MoodRecord.fromMap(maps.first, attachments: attachments);
    }
    return null;
  }

  /// 获取所有情绪记录
  /// [limit] 限制返回的记录数量
  /// [offset] 偏移量
  /// [orderBy] 排序字段
  /// 返回情绪记录列表
  Future<List<MoodRecord>> getAllMoodRecords({
    int? limit,
    int? offset,
    String orderBy = 'created_at DESC',
  }) async {
    final db = await database;
    final maps = await db.query(
      moodRecordsTable,
      orderBy: orderBy,
      limit: limit,
      offset: offset,
    );

    final records = <MoodRecord>[];
    for (final map in maps) {
      final id = map['id'] as int;
      final attachments = await getMediaAttachmentsByRecordId(id);
      records.add(MoodRecord.fromMap(map, attachments: attachments));
    }
    return records;
  }

  /// 根据日期范围获取情绪记录
  /// [startDate] 开始日期
  /// [endDate] 结束日期
  /// 返回指定日期范围内的情绪记录列表
  Future<List<MoodRecord>> getMoodRecordsByDateRange(
    DateTime startDate,
    DateTime endDate,
  ) async {
    final db = await database;
    final maps = await db.query(
      moodRecordsTable,
      where: 'created_at >= ? AND created_at <= ?',
      whereArgs: [
        startDate.toIso8601String(),
        endDate.toIso8601String(),
      ],
      orderBy: 'created_at DESC',
    );

    final records = <MoodRecord>[];
    for (final map in maps) {
      final id = map['id'] as int;
      final attachments = await getMediaAttachmentsByRecordId(id);
      records.add(MoodRecord.fromMap(map, attachments: attachments));
    }
    return records;
  }

  /// 根据情绪类型获取记录
  /// [moodType] 情绪类型
  /// 返回指定情绪类型的记录列表
  Future<List<MoodRecord>> getMoodRecordsByType(MoodType moodType) async {
    final db = await database;
    final maps = await db.query(
      moodRecordsTable,
      where: 'mood_type = ?',
      whereArgs: [moodType.toString().split('.').last],
      orderBy: 'created_at DESC',
    );

    final records = <MoodRecord>[];
    for (final map in maps) {
      final id = map['id'] as int;
      final attachments = await getMediaAttachmentsByRecordId(id);
      records.add(MoodRecord.fromMap(map, attachments: attachments));
    }
    return records;
  }

  /// 搜索情绪记录
  /// [keyword] 搜索关键词
  /// 返回包含关键词的记录列表
  Future<List<MoodRecord>> searchMoodRecords(String keyword) async {
    final db = await database;
    final maps = await db.query(
      moodRecordsTable,
      where: 'diary_content LIKE ? OR tags LIKE ?',
      whereArgs: ['%$keyword%', '%$keyword%'],
      orderBy: 'created_at DESC',
    );

    final records = <MoodRecord>[];
    for (final map in maps) {
      final id = map['id'] as int;
      final attachments = await getMediaAttachmentsByRecordId(id);
      records.add(MoodRecord.fromMap(map, attachments: attachments));
    }
    return records;
  }

  /// 插入媒体附件
  /// [attachment] 要插入的媒体附件
  /// 返回插入记录的ID
  Future<int> insertMediaAttachment(MediaAttachment attachment) async {
    final db = await database;
    return await db.insert(mediaAttachmentsTable, attachment.toMap());
  }

  /// 删除媒体附件
  /// [id] 要删除的附件ID
  /// 返回受影响的行数
  Future<int> deleteMediaAttachment(int id) async {
    final db = await database;
    return await db.delete(
      mediaAttachmentsTable,
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 根据情绪记录ID获取媒体附件
  /// [recordId] 情绪记录ID
  /// 返回关联的媒体附件列表
  Future<List<MediaAttachment>> getMediaAttachmentsByRecordId(int recordId) async {
    final db = await database;
    final maps = await db.query(
      mediaAttachmentsTable,
      where: 'mood_record_id = ?',
      whereArgs: [recordId],
      orderBy: 'created_at ASC',
    );

    return maps.map((map) => MediaAttachment.fromMap(map)).toList();
  }

  /// 获取数据库统计信息
  /// 返回包含记录总数、最早记录日期等信息的Map
  Future<Map<String, dynamic>> getDatabaseStats() async {
    final db = await database;
    
    // 获取记录总数
    final countResult = await db.rawQuery('SELECT COUNT(*) as count FROM $moodRecordsTable');
    final totalRecords = countResult.first['count'] as int;
    
    // 获取最早记录日期
    final earliestResult = await db.rawQuery(
      'SELECT MIN(created_at) as earliest FROM $moodRecordsTable'
    );
    final earliestDate = earliestResult.first['earliest'] as String?;
    
    // 获取最新记录日期
    final latestResult = await db.rawQuery(
      'SELECT MAX(created_at) as latest FROM $moodRecordsTable'
    );
    final latestDate = latestResult.first['latest'] as String?;
    
    return {
      'totalRecords': totalRecords,
      'earliestDate': earliestDate,
      'latestDate': latestDate,
    };
  }

  /// 清除所有记录
  /// 删除所有情绪记录和媒体附件
  Future<void> clearAllRecords() async {
    final db = await database;
    await db.transaction((txn) async {
      // 删除所有媒体附件
      await txn.delete(mediaAttachmentsTable);
      // 删除所有情绪记录
      await txn.delete(moodRecordsTable);
    });
  }

  /// 关闭数据库连接
  Future<void> close() async {
    final db = _database;
    if (db != null) {
      await db.close();
      _database = null;
    }
  }

  /// 删除数据库文件（用于测试或重置）
  Future<void> deleteDatabase() async {
    final databasesPath = await getDatabasesPath();
    final path = join(databasesPath, _databaseName);
    await databaseFactory.deleteDatabase(path);
    _database = null;
  }
}